using System.Threading.Tasks;
using Dapper;
using Microsoft.EntityFrameworkCore;
using Project.Infrastructure;

namespace Project.API.Application.Queries
{
    public class ProjectQuery:IProjectQuery
    {
        private readonly ProjectContext _dbContext;

        public ProjectQuery(ProjectContext projectContext)
        {
            _dbContext = projectContext;
        }
        
        public async Task<dynamic> GetProjectsByUserIdAsync(int userId)
        {
            using (var conn = _dbContext.Database.GetDbConnection())
            {
                conn.Open();
                var sql = @"SELECT 
                                Id,
                                Avatar,
                                Company,
                                FinStage,
                                Introduction,
                                ShowSecurityInfo,
                                CreatedTime
                            FROM Projects WHERE UserId =@userId";

                var result = await conn.QueryAsync<dynamic>(sql, new { userId });
                return result;
            }
        }

        public async Task<dynamic> GetProjectDetailAsync(int projectId)
        {
            using (var conn = _dbContext.Database.GetDbConnection())
            {
                conn.Open();
                var sql = @"SELECT 
                                Projects.Company,
                                Projects.CityName,
                                Projects.ProvinceName,
                                Projects.AreaName,
                                Projects.FinStage,
                                Projects.FinMoney,
                                Projects.Valuation,
                                Projects.FinPercentage,
                                Projects.Introduction,
                                Projects.UserId,
                                Projects.Income,
                                Projects.Revenue,
                                Projects.Avatar,
                                Projects.BrokerageOptions,
                                ProjectVisibleRules.Tags,
                                ProjectVisibleRules.Visible
                            FROM  Projects 
                            INNER JOIN ProjectVisibleRules ON Projects.Id  = ProjectVisibleRules.ProjectId
                            WHERE Projects.Id =@projectId ";
                
                var result =  await conn.QueryAsync<dynamic>(sql, new { projectId });
                return result;
            }
        }

        public async Task<dynamic> GetMyProjectDetailAsync(int projectId, int userId)
        {
            using (var conn = _dbContext.Database.GetDbConnection())
            {
                conn.Open();
                var sql = @"SELECT 
                                Projects.Company,
                                Projects.CityName,
                                Projects.ProvinceName,
                                Projects.AreaName,
                                Projects.FinStage,
                                Projects.FinMoney,
                                Projects.Valuation,
                                Projects.FinPercentage,
                                Projects.Introduction,
                                Projects.UserId,
                                Projects.Income,
                                Projects.Revenue,
                                Projects.Avatar,
                                Projects.BrokerageOptions,
                                ProjectVisibleRules.Tags,
                                ProjectVisibleRules.Visible
                            FROM  Projects 
                            INNER JOIN ProjectVisibleRules ON Projects.Id  = ProjectVisibleRules.ProjectId
                            WHERE Projects.Id =@projectId And Projects.UserId=@userId ";
                
                var result =  await conn.QueryFirstOrDefaultAsync<dynamic>(sql, new { projectId,userId });
                return result;
            }
        }
    }
}